from typing import List
import re
import threading

import pymysql

from component.ConfigManager import config_manager
from .exception import *


'''
TODO 如何防止sql注入：
https://blog.csdn.net/qq_43874317/article/details/127752524
https://shockerli.net/post/python3-pymysql/[重点参考]

TODO 加入持久化连接能力：MySQL持久化链接保持时间为8小时(28800秒），过期后断开连， https://www.cnblogs.com/meloncodezhang/p/12567738.html
TODO 加入锁和事务处理
TODO 优化mysql库, 需要加入以下能力：
1、加入连接池的能力 https://blog.csdn.net/m0_46926492/article/details/124231269
2、加入主从数据库的读写能力（db拥有读写能力，新增一个readb仅拥有读能力）。。。。

https://webwareforpython.github.io/DBUtils/main.html
from dbutils.pooled_db import PooledDB

'''

lock = threading.Lock()

class MysqlConnector(object):
    _instance = None
    def __new__(cls, *args, **kwargs): #单例模式
        if cls._instance is None:  
            cls._instance = super().__new__(cls, *args, **kwargs)
        return cls._instance 
    
    def __init__(self) -> None:
        self.cursor, self.dbconn = None, None
        try:
            self.dbconn = pymysql.connect(host=config_manager.get_value("mysql", "host"),
                                    user=config_manager.get_value("mysql", "user"),
                                    password=config_manager.get_value("mysql", "password"),
                                    database=config_manager.get_value("mysql", "database"),
                                    charset=config_manager.get_value("mysql", "charset"))
                                    # cursorclass=pymysql.cursors.DictCursor)
            self.cursor = self.dbconn.cursor() # TODO 后续改为 pymysql.cursors.DictCursor
        except Exception as e:
            raise DatabaseError(e)
        
        
    def __del__(self) -> None:
        if self.cursor is not None:
            self.cursor.close()
        if self.dbconn is not None:
            self.dbconn.close()


    # 查询操作，用于select
    # is_dict参数：是否返回列表名字典化处理；False表示仅返回数据列表，无需字典化，用于一些查询记录总数的场景
    def query(self, sql: str, is_dict: bool = True) -> List:
        print(re.sub("\n|\t|\s+", " ",sql))
        try:
            lock.acquire()
            rowcount = self.cursor.execute(sql)
            fields = self.cursor.description # table column field description
            lock.release()
            data = self.cursor.fetchall()
            print("fetch row:",rowcount)
        except Exception as e:
            lock.release()
            raise DatabaseError(e)
        if not is_dict:
            return data
        data_dict = [dict(zip([col[0] for col in fields], record)) for record in data]
        assert len(data_dict) == rowcount
        return data_dict


    # 事务处理，用于insert和update、delete操作
    def execute(self, sql: str, is_insert: bool = True) -> tuple:
        print(re.sub("\n|\t|\s+", " ",sql))
        try:
            lock.acquire()
            affectrows = self.cursor.execute(sql)
            self.dbconn.commit()
            lock.release()
        except Exception as e:
            self.dbconn.rollback()
            lock.release()
            if re.search('Duplicate entry.*key', str(e)) is not None:
                print(str(e))
                raise DuplicateError(e)
            else:
                print(e)
                raise DatabaseError(e)
        if is_insert:
            return self.cursor.lastrowid, affectrows # self.cursor.rowcount TODO 查明白这两者的区别
        else:
            return 0, affectrows  # update/delete 执行失败时则affectrows=0, 非0则表示成功


    # 仅用于select count() 操作，比query()函数中fetchall效率高，适用于计算page_total
    def count(self, count_condition: str, table_name: str) -> int:
        sql = "select count(%s) as count from %s" % (count_condition, table_name)
        print("count_sql:", sql)
        try:
            lock.acquire()
            self.cursor.execute(sql)
            lock.release()
            count = self.cursor.fetchone()[0] # fetchone返回tuple(count,)
        except Exception as e:
            lock.release()
            raise DatabaseError(e)
        return count



mysqlconnector: MysqlConnector = MysqlConnector()

__all__ = [mysqlconnector]